﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DisciplinaLib.Basic;
using System.Data;

namespace DisciplinaLib.Repositorio
{
    public class RepDisciplina
    {
        private SqlConnection conexao;
        private string stringConexao = @"Data Source=VITOR-PC\SQLEXPRESS;Initial Catalog=Escola; user id=sa; pwd=jva;";
        private static RepDisciplina instancia;

        private RepDisciplina()
        {
        }


        public static RepDisciplina getInstancia()
        {
            if (instancia == null)
            {
                instancia = new RepDisciplina();
                return instancia;
            }
            return instancia;
        }


        public void inserir(Disciplina obj)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("insert into Disciplina values (@NomeDisc, @CargaHor)", conexao);

                sql.Parameters.AddWithValue("@NomeDisc", obj.Nome);
                sql.Parameters.AddWithValue("@CargaHor", obj.Ch);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }

        }

        public void alterar(Disciplina obj)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("update Disciplina set NomeDisc=@NomeDisc, CargaHor=@CargaHor where CodDisc=@CodDisc", conexao);

                sql.Parameters.AddWithValue("@CodDisc", obj.Codigo);
                sql.Parameters.AddWithValue("@NomeDisc", obj.Nome);
                sql.Parameters.AddWithValue("@CargaHor", obj.Ch);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<Disciplina> listar()
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Disciplina> lista = new List<Disciplina>();
                SqlCommand sql = new SqlCommand("select * from Disciplina order by CodDisc", conexao);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Disciplina disciplina = new Disciplina();
                    disciplina.Codigo = Convert.ToInt32(dr["CodDisc"]);
                    disciplina.Nome = dr["NomeDisc"].ToString();
                    disciplina.Ch = Convert.ToInt32(dr["CargaHor"]);
                    lista.Add(disciplina);
                }
                return lista;
               
            }
            catch (Exception e)
            {

                throw e;
            }


            finally
            {

                conexao.Dispose();

            }
        }

        public List<Disciplina> listarAlterar(Disciplina obj)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Disciplina> lista = new List<Disciplina>();
                SqlCommand sql = new SqlCommand("select * from Disciplina where CodDisc!= @CodDisc", conexao);

                sql.Parameters.AddWithValue("@CodDisc", obj.Codigo);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Disciplina disciplina = new Disciplina();
                    disciplina.Codigo = Convert.ToInt32(dr["CodDisc"]);
                    disciplina.Nome = dr["NomeDisc"].ToString();
                    disciplina.Ch = Convert.ToInt32(dr["CargaHor"]);
                    lista.Add(disciplina);
                }
                return lista;

            }
            catch (Exception e)
            {

                throw e;
            }


            finally
            {

                conexao.Dispose();

            }
        }

        public void excluir(Disciplina obj)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {

                SqlCommand sql = new SqlCommand("delete from Disciplina where CodDisc=@CodDisc", conexao);

                sql.Parameters.AddWithValue("@CodDisc", obj.Codigo);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Não é possível excluir, existe um Professor ou Turma cadastrado para essa Disciplina!");
            }

            finally
            {
                conexao.Dispose();
            }
        }
    }
}
